from sqlalchemy import create_engine, Column, String, Integer, Float, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

engine = create_engine("mysql+pymysql://hu:hu@192.168.0.111/scroe", echo=True)
Base = declarative_base()


class Student(Base):
    __tablename__ = 'student'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(16), nullable=False)
    password = Column(String(255), nullable=False)

    def __repr__(self):
        return '<student name:{}>'.format(self.name)


class Teacher(Base):
    __tablename__ = 'teacher'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(16), nullable=False)
    password = Column(String(255), nullable=False)

    def __repr__(self):
        return '<teacher name:{}>'.format(self.name)


class Score(Base):
    __tablename__ = 'score'
    id = Column(Integer, ForeignKey('student.id', ondelete='RESTRICT'), primary_key=True, autoincrement=True)
    Chinese = Column(Float(10), nullable=False)
    Math = Column(Float(10), nullable=False)
    English = Column(Float(10), nullable=False)

    def __repr__(self):
        return '<student id:{} Chinese:{} Math:{} English:{}>'.format(self.id, self.Chinese, self.Math, self.English)


Session = sessionmaker(bind=engine)
session = Session()


class Score_Query:

    def __init__(self, _session: Session):
        self.session = _session
        self._flag = 0
        self._count = {}

    def _validate(self, character):
        user = self.session.query(character).filter(character.id == self.identity).first()
        if user:
            if self._count.get(user.name, 0) > 2:
                print('用户已锁定，请使用其他用户登录')
                return False

            password = input('>>请输入密码').strip()
            if user.password == password:
                self._flag = 1
                return self._flag

            else:
                print('用户名密码错误')
                self._count[user.name] = self._count.get(user.name, 0) + 1
                return False

        else:
            print('用户名密码错误')
            return False

    def run(self):
        while True:
            status = input('>>teacher or student or close').strip()

            setattr(self, 'identity', input('>>请输入id').strip())

            if status.lower() == 'teacher':
                if self._validate(Teacher):
                    self._input()
                else:
                    continue

            elif status.lower() == 'student':
                if self._validate(Student):
                    self._query()
                else:
                    continue

            elif status == 'close':
                return

            else:
                print("请输入有效值")
                continue

    def _input(self):
        if self._flag != 1:
            print('无权限操作')
            return

        def check(score):
            if score < 0 or score > 100:
                raise Exception('分数有误')
            else:
                return score

        if input('>>是否输入成绩y/n').strip() == 'y':
            while True:
                identity = input('>>请输入id').strip()
                user = self.session.query(Student).filter(Student.id == identity).first()
                if user:
                    Chinese = int(input('>>请输入语文成绩').strip())
                    Math = int(input('>>请输入数学成绩').strip())
                    English = int(input('>>请输入英语成绩').strip())
                    try:
                        score = self.session.query(Score).filter(Score.id == identity).first()
                        if not score:
                            score = Score(Chinese=Chinese, Math=Math, English=English)
                            self.session.add(score)

                        else:
                            score.Chinese = check(Chinese)
                            score.Math = check(Math)
                            score.English = check(English)
                            self.session.add(score)

                        if input('>>是否继续添加成绩y/n') == 'n':
                            self.session.commit()
                            break

                    except Exception as e:
                        print(e, type(e))

                else:
                    print('学生名不存在')
                    continue

        self._query()

    def _query(self):
        if input('>>是否查询成绩y/n') == 'n':
            return
        while True:

            identity = input('>>请输入id').strip()
            score = self.session.query(Score).filter(Score.id == identity).first()
            if score:
                print("语文：{} 数学：{} 英语：{}".format(score.Chinese, score.Math, score.English))

                if input('>>是否继续查询y/n') == 'n':
                    break

            else:
                print('未查到结果')


a = Score_Query(session)
a.run()